#!/usr/bin/env python
# coding=utf-8
# __author__ = 'Yunchao Ling'

import MySQLdb
from pymongo import MongoClient
from tqdm import tqdm


# 连接MySQL
def InitMySQL():
    DATABASE_NAME = 'plosp'
    HOST = '10.188.188.22'
    USER_NAME = 'plosp'
    PASSWORD = 'plosp'
    CHAR_SET = 'utf8'
    conn = MySQLdb.connect(host=HOST, user=USER_NAME, passwd=PASSWORD, db=DATABASE_NAME, charset=CHAR_SET)
    return conn


# 关闭MySQL
def CloseMySQL(conn):
    conn.close()


# 连接MongoDB
def InitMongoDB():
    MONGODB_HOST = '10.188.188.100'
    MONGODB_PORT = 27017
    MONGODB_DB_NAME = 'journal'
    MONGODB_COLLECTION_NAME = 'journal'
    connection = MongoClient(MONGODB_HOST, MONGODB_PORT)
    db = connection[MONGODB_DB_NAME]
    collection = db[MONGODB_COLLECTION_NAME]
    return connection, collection


# 关闭MongoDB
def CloseMongoDB(MongoDB_Connection):
    MongoDB_Connection.close()


def ISSN2JournalID():
    conn = InitMySQL()
    inputfile = open('D:/data/AMMS_journal_2015_mongo.txt', 'r')
    outfile = open('D:/data/AMMS_journal_2015_mongo_mysql.txt', 'w')
    for line in tqdm(inputfile):
        line = line.rstrip()
        outline = line + "\t"
        issns = line.split("\t")[1]
        issnin = "("
        for splitissn in issns.split(","):
            issnin += "'" + splitissn + "',"
        issnin = issnin[:-1] + ")"
        cursor = conn.cursor()
        sql = "SELECT id FROM tb_dds_journal WHERE issn_print IN " + issnin + " OR issn_electronic IN " + issnin
        # params = (line)
        # cursor.execute(sql, params)
        cursor.execute(sql)
        result = cursor.fetchone()
        if result != None:
            outline += str(result[0])
        conn.commit()
        cursor.close()
        outfile.write(outline + "\n")
    inputfile.close()
    outfile.close()
    CloseMySQL(conn)


# def Title2ISSN():
# inputfile = open('D:/data/AMMS_journal_2015.txt', 'r')
# outputfile=open('D:/data/AMMS_journal_2015_mongo.txt', 'w')

# count=0
# outline=line+"\t"
# results=collection.find({"FullTitle":line})
# for result in results:
#     if result.has_key('ISSNSet'):
#         issnset=''
#         for item in result["ISSNSet"]:
#             issnset+=item+","
#         outline+=issnset
#         outline=outline[:-1]
# outputfile.write(outline+"\n")

# outputfile.close()
# connection.close()

def JournalID2Stat():
    conn = InitMySQL()

    inputfile = open('D:/data/AMMS_journal_2015_mongo_mysql.txt', 'r')
    outputfile = open('D:/data/AMMS_journal_2015_stat.txt', 'w')

    for line in inputfile:
        line = line.rstrip("\n")
        outline = line + "\t"
        journal_id = line.split("\t")[2].strip()
        if journal_id != "":
            cursor = conn.cursor()
            sql = "SELECT count(*) FROM tb_dds_article WHERE journal_id = %s" % journal_id
            cursor.execute(sql)
            result = cursor.fetchone()
            if result != None:
                outline += str(result[0])
            conn.commit()
            cursor.close()
        outputfile.write(outline + "\n")
        outputfile.flush()
    inputfile.close()
    outputfile.close()
    CloseMySQL(conn)


if __name__ == "__main__":
    JournalID2Stat()